package com.turing.post.report.impl;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.report.util.BranchDaySett;
import com.turing.post.report.util.BranchDaySettDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;

@Stateless(mappedName = "report.impl.BranchDaySettDaoImpl")
@Remote
@Local
public class BranchDaySettDaoImpl implements BranchDaySettDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public void insert(BranchDaySett bds) throws PCPSApplicationException {
		GeneralDao.insert(_em, bds);
	}

	@Override
	public void removeBranchDaySett(String saleBranchNo, Date tranDate) {
		Query query = _em
				.createNativeQuery("delete from BranchDaySett t where t.saleBranchNo = ?1 and t.tranDate = ?2");
		query.setParameter(1, saleBranchNo);
		query.setParameter(2, tranDate);
		query.executeUpdate();
	}

	/**
	 * 根据条件查询卡商某销售机构某日业务日结数据
	 * 
	 * @param saleBranchNo
	 *            销售机构
	 * @param startTranDate
	 *            起始日期
	 * @param endTranDate
	 *            终止日期
	 * @throws PCPSApplicationException
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getGenerateBranchDaySettList(String saleBranchNo,
			String startTranDate, String endTranDate) {
		if (PCPSUtil.isEmpty(saleBranchNo) || PCPSUtil.isEmpty(startTranDate)
				|| PCPSUtil.isEmpty(endTranDate))
			return null;
		String sql = "select TranType,nvl(sum(TranCount),0),nvl(sum(CashAmount),0),nvl(sum(TranAmount),0),nvl(sum(ChequeAmount),0),nvl(sum(redempAmount),0),nvl(sum(creditAmount),0),nvl(sum(CashFee),0),nvl(sum(TranFee),0),nvl(sum(ChequeFee),0),nvl(sum(CardFee),0),nvl(sum(creditFee),0),nvl(sum(DiscountFee),0) from BranchDaySett "
				+ " where saleBranchNo in ("
				+ saleBranchNo
				+ ") and TranDate>=to_date('"
				+ startTranDate
				+ "','yyyy-mm-dd') and TranDate<=to_date('"
				+ endTranDate
				+ "','yyyy-mm-dd') and TranType not in('"
				// 西邮寄要求日结扎帐中不显示现金上缴结果
				+ BranchDaySett.TRANTYPE_DELIVERY + "')";
		sql = sql + " group by TranType order by TranType asc ";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@Override
	public BigInteger getFeeTotalBySaleBrchNos(String saleBrchNos,
			String startDate, String endDate) {
		String sql = "select nvl(sum(t.cashfee+t.tranfee+t.chequefee),0)"
				+ " from branchdaysett t where t.salebranchno in ("
				+ saleBrchNos + ")"
				+ " and t.trandate >=to_date(?1,'yyyy-MM-dd')"
				+ " and t.trandate <=to_date(?2,'yyyy-MM-dd')";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return new BigInteger(query.getSingleResult().toString());
	}
}